package com.gack.business.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.gack.business.model.Contacts;
import com.gack.business.vo.UserItemVO;

@Repository
public interface ContactsRepository extends JpaRepository<Contacts, String>,JpaSpecificationExecutor<Contacts>{

	@Query("select new com.gack.business.vo.UserItemVO(c.useridFriendId, '', '', c.friendRemarks)"
			+ " from Contacts c"
			+ " where c.type = 1"
			+ " and c.userUsername = (select u.username"
										+ " from User u "
										+ "where u.id=:userId)"
			+ " and c.useridFriendId in (select uedp.userId"
										+ " from UserEnterpriseDepartmentPosition uedp"
										+ " where uedp.enterpriseId=:enterpriseId)"
			+ " and c.friendRemarks like %:inputName%")
	List<UserItemVO> findUserItemVOByInputName(@Param("userId") String userId, @Param("enterpriseId") String enterpriseId, @Param("inputName") String inputName);//查询该用户的好友备注中匹配该输入名称的用户ItemVO(id, '', '', 好友备注)
	
	@Query("select new com.gack.business.vo.UserItemVO(c.useridFriendId, '', '', c.friendRemarks)"
			+ " from Contacts c"
			+ " where c.type = 1"
			+ " and c.userUsername = (select u.username"
										+ " from User u"
										+ " where u.id=:userId)"
			+ " and c.useridFriendId in (select uedp.userId"
										+ " from UserEnterpriseDepartmentPosition uedp"
										+ " where uedp.enterpriseId=:enterpriseId"
										+ " and (uedp.departmentId = null or uedp.departmentId = '') )"
			+ " and c.friendRemarks != null"
			+ " and c.friendRemarks != ''")
	List<UserItemVO> findNoDepartmentUserItemVO(@Param("enterpriseId") String enterpriseId, @Param("userId") String userId);//查询无部门用户ItemVO(id, '', '', 好友备注) 
	
	@Query("select u from Contacts u   where u.userUsername=:userUsername and u.useridFriendId=:useridFriendId")
	public Contacts findContactsfor(@Param("userUsername") String userUsername, @Param("useridFriendId") String useridFriendId);
	
	@Query("select count(c.id)"
			+ " from Contacts c"
			+ " where c.type = 1"
			+ " and c.userUsername=(select u.username"
										+ " from User u"
										+ " where u.id=:userId1)"
			+ " and c.useridFriendId=:userId2")
	int countByUserIdAndUserId(@Param("userId1") String userId1, @Param("userId2") String userId2);//查询userId2是否为userId1的好友
	
	@Query("select new com.gack.business.vo.UserItemVO(c.useridFriendId, '', '', c.friendRemarks)"
			+ " from Contacts c"
			+ " where c.type = 1"
			+ " and c.friendRemarks != null"
			+ " and c.friendRemarks != ''"
			+ " and c.userUsername = (select u.username"
										+ " from User u"
										+ " where u.id=:userId)"
			+ " and c.useridFriendId in (select uedp.userId"
											+ " from UserEnterpriseDepartmentPosition uedp"
											+ " where uedp.departmentId=:departmentId)")
	List<UserItemVO> findUserItemVOByDepartmentId(@Param("departmentId") String departmentId, @Param("userId") String userId);//查询该部门的人员ItemVO(id, '', '', 好友备注)
	
	@Query("from Contacts c"
			+ " where c.type = 1"
			+ " and c.userUsername = (select u.username"
										+ " from User u"
										+ " where u.id=:showToUserId)"
			+ " and c.useridFriendId=:userId")
	List<Contacts> findByUserIdAndShowTOUserId(@Param("userId") String userId, @Param("showToUserId") String showToUserId);//查询userId是否为showToUserId的好友
	
	@Query("select u from Contacts u   where  u.useridFriendId=:useridFriendId and u.type=0")
	public List<Contacts> findContactsforuseridFriendId(@Param("useridFriendId") String useridFriendId);
	

	@Query("select u from Contacts u   where u.useridFriendUsername=:useridFriendUsername and u.userUsername=:userUsername")

	public List<Contacts> findContactsforusername(@Param("useridFriendUsername") String useridFriendUsername,@Param("userUsername") String userUsername);
	

	@Modifying
	@Query("update Contacts c set c.friendRemarks=:friendRemarks"
			+ " where c.useridFriendId=:userId"
			+ " and c.type = 1"
			+ " and c.userUsername = (select u.username"
										+ " from User u"
										+ " where u.id=:operatorId)")
	int updateFriendRemarks(@Param("userId") String userId, @Param("operatorId") String operatorId, @Param("friendRemarks") String friendRemarks);//修改某用户的好友备注

	@Query("select u from Contacts u   where  u.useridFriendUsername=:useridFriendUsername and u.type=3")
	public List<Contacts> findContactsforuseriduseridFriendUsername(@Param("useridFriendUsername") String useridFriendUsername);
	
	@Query("select friendRemarks"
			+ " from Contacts"
			+ " where userUserid=:showToUserId"
			+ " and useridFriendId=:userId"
			+ " and type = 1")
	String findFriendRemarks(@Param("userId")String userId, @Param("showToUserId")String showToUserId);//显示shotToUserId对userId设置的好友备注
	
}
